package jnpf.base.service.impl;


import com.baomidou.mybatisplus.annotation.DbType;
import jnpf.exception.DataException;
import jnpf.base.ActionResult;
import jnpf.base.service.DbTableService;
import jnpf.base.service.DblinkService;
import jnpf.config.ConfigValueUtil;
import jnpf.base.entity.DbLinkEntity;
import jnpf.base.model.dbtable.DbTableDataForm;
import jnpf.base.model.dbtable.DbTableFieldModel;
import jnpf.base.model.dbtable.DbTableModel;
import jnpf.base.util.*;
import jnpf.util.*;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 数据管理
 *
 * @author 南通渡课开发平台组
 * @version V3.1.0
 * @copyright 南通渡课信息技术有限公司
 * @date 2019年9月27日 上午9:18
 */
@Slf4j
@Service
public class DbTableServiceImpl implements DbTableService {

    @Autowired
    private DblinkService dblinkService;
    @Autowired
    private DataSourceUtil dataSourceUtils;
    @Autowired
    private UserProvider userProvider;
    @Autowired
    private ConfigValueUtil configValueUtil;
    @Autowired
    private DataSourceUtil dataSourceUtil;

    @Override
    public List<DbTableModel> getList(String dbId) throws DataException {
        List<DbTableModel> list = new ArrayList<>();
        try {
            @Cleanup Connection conn = null;
            DbLinkEntity link = dblinkService.getInfo(dbId);
            String tenSource = "";
            if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
                tenSource = userProvider.get().getTenantDbConnectionString();
            }
            if (link != null) {
                String dnname = link.getServiceName();
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        list = DbUtil.mysqlGetList(conn, dnname);
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        list = DbUtil.sqlServerGetList(conn, dnname);
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        list = DbUtil.oracleGetList(conn, dnname);
                    }else if(link.getDbType().toLowerCase().equals(DbType.DM.getDb())){
                        list = DbUtil.dmGetList(conn, dnname);
                    }
                }
            } else {
                String dbName = dataSourceUtils.getDbName();
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                if (StringUtil.isNotEmpty(tenSource)) {
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , tenSource);
                    dbName = tenSource;
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                if (conn != null) {
                    if (dataSourceUtils.getUrl().contains("mysql")) {
                        list = DbUtil.mysqlGetList(conn, dbName);
                    } else if (dataSourceUtils.getUrl().contains("sqlserver")) {
                        list = DbUtil.sqlServerGetList(conn, dbName);
                    } else if (dataSourceUtils.getUrl().contains("oracle")) {
                        list = DbUtil.oracleGetList(conn, dbName);
                    }else if (dataSourceUtils.getUrl().contains("dm")){
                        list = DbUtil.dmGetList(conn, dbName);
                    }
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
        return list;
    }

    @Override
    public List<DbTableFieldModel> getFieldList(String dbId, String table) throws DataException {
        DbLinkEntity link = dblinkService.getInfo(dbId);
        List<DbTableFieldModel> list = new ArrayList<>();
        try {
            @Cleanup Connection conn = null;
            String tenSource = "";
            if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
                tenSource = userProvider.get().getTenantDbConnectionString();
            }
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        list = DbUtil.getMysqlFieldList(conn, link.getServiceName(), table);
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        list = DbUtil.getSqlserverFieldList(conn, table);
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        list = DbUtil.getOrcalFieldList(conn, table);
                    }else if (link.getDbType().toLowerCase().equals(DbType.DM.getDb())){
                        list = DbUtil.getDmFieldList(conn, table);
                    }
                }
            } else {
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , tenSource);
                if (StringUtil.isEmpty(tenSource)) {
                    String dbName = dataSourceUtils.getDbName();
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                    tenSource = dataSourceUtils.getDbName();
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                if (conn != null) {
                    if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.MYSQL.getDb())) {
                        list = DbUtil.getMysqlFieldList(conn, tenSource, table);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.SQL_SERVER.getDb())) {
                        list = DbUtil.getSqlserverFieldList(conn, table);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.ORACLE.getDb())) {
                        list = DbUtil.getOrcalFieldList(conn, table);
                    }else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.DM.getDb())){
                        list = DbUtil.getDmFieldList(conn, table);
                    }
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
        return list;
    }

    @Override
    public List<Map<String, Object>> getData(DbTableDataForm dbTableDataForm, String dbId, String table) {
        String dbName = "";
        if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
            dbName = userProvider.get().getTenantDbConnectionString();
        }
        DbLinkEntity link = dblinkService.getInfo(dbId);
        List<Map<String, Object>> list = new ArrayList();
        try {
            @Cleanup Connection conn=null;
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        list = DbUtil.mysqlGetData(conn, table, dbTableDataForm);
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        list = DbUtil.sqlserverGetData(conn, table, dbTableDataForm);
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        list = DbUtil.orcalGetData(conn, table, dbTableDataForm);
                    } else if (link.getDbType().toLowerCase().equals(DbType.DM.getDb())) {
                        list = DbUtil.dmGetData(conn, table, dbTableDataForm);
                    }
                }
            } else {
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                if (StringUtil.isEmpty(dbName)) {
                    dbName = dataSourceUtils.getDbName();
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                List<Map<String, Object>> getList;
                StringBuilder sql = new StringBuilder();
                if (conn != null) {
                    if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.MYSQL.getDb())) {
                        sql.append("select * from " + dbName + "." + table);
                        if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(dbTableDataForm.getField())) {
                            sql.append(" where " + dbTableDataForm.getField() + " like '%" + dbTableDataForm.getKeyword() + "%'");
                        }
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.SQL_SERVER.getDb())) {
                        sql.append("select * from " + dbName + ".dbo." + table);
                        if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(dbTableDataForm.getField())) {
                            sql.append(" where " + dbTableDataForm.getField() + " like '%" + dbTableDataForm.getKeyword() + "%'");
                        }
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.ORACLE.getDb())) {
                        sql.append("select * from " + dataSourceUtils.getUserName().split(" ")[0] + "." + table);
                        if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(dbTableDataForm.getField())) {
                            sql.append(" where " + dbTableDataForm.getField() + " like '%" + dbTableDataForm.getKeyword() + "%'");
                        }
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.DM.getDb())) {
                        sql.append("select * from " + dbName + "." + table);
                        if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(dbTableDataForm.getField())) {
                            sql.append(" where " + dbTableDataForm.getField() + " like '%" + dbTableDataForm.getKeyword() + "%'");
                        }
                    }
                    ResultSet query = null;
                    try {
                        query = JdbcUtil.query(conn, sql.toString());
                    } catch (Exception e) {
                        log.error(e.getMessage());
                    }
                    getList = JdbcUtil.convertListString(query);
                    for (Map<String, Object> mapList : getList) {
                        Map<String, Object> objectMap = new HashMap<>(16);
                        for (String key : mapList.keySet()) {
                            objectMap.put(key.toLowerCase(), mapList.get(key));
                        }
                        list.add(objectMap);
                    }
                }
            }
        } catch (Exception e) {

        }
        return dbTableDataForm.setData(PageUtil.getListPage((int) dbTableDataForm.getCurrentPage(), (int) dbTableDataForm.getPageSize(), list), list.size());
    }

    @Override
    public boolean isExistByFullName(String dbId, String table, String oldTable) throws DataException {
        List<DbTableModel> data = this.getList(dbId).stream().filter(m -> m.getTable().equals(table)).collect(Collectors.toList());
        if (!StringUtil.isEmpty(oldTable)) {
            data = data.stream().filter(m -> !m.getTable().equals(oldTable)).collect(Collectors.toList());
        }
        return data.size() > 0;
    }

    @Override
    public String getDbTime(String dbId) throws DataException {
        DbLinkEntity link = dblinkService.getInfo(dbId);
        String time = "";
        try {
            @Cleanup Connection conn = null;
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        StringBuilder sql = new StringBuilder();
                        sql.append("SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') as TIME");
                        ResultSet result = JdbcUtil.query(conn, sql.toString());
                        try {
                            while (result.next()) {
                                time = result.getString("TIME");
                            }
                            return time;
                        } catch (Exception e) {
                            e.getMessage();
                        }
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        StringBuilder sql = new StringBuilder();
                        sql.append("Select CONVERT(varchar(100), GETDATE(), 120) as TIME");
                        ResultSet result = JdbcUtil.query(conn, sql.toString());
                        try {
                            while (result.next()) {
                                time = result.getString("TIME");
                            }
                            return time;
                        } catch (Exception e) {
                            e.getMessage();
                        }
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        StringBuilder sql = new StringBuilder();
                        sql.append("select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as TIME from dual");
                        ResultSet result = JdbcUtil.query(conn, sql.toString());
                        try {
                            while (result.next()) {
                                time = result.getString("TIME");
                            }
                            return time;
                        } catch (Exception e) {
                            e.getMessage();
                        }
                    }else if(link.getDbType().toLowerCase().equals(DbType.DM.getDb())){
                        StringBuilder sql = new StringBuilder();
                        sql.append("select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as TIME ");
                        ResultSet result = JdbcUtil.query(conn, sql.toString());
                        try {
                            while (result.next()) {
                                time = result.getString("TIME");
                            }
                            return time;
                        } catch (Exception e) {
                            e.getMessage();
                        }
                    }
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
        return time;
    }

    @Override
    public int executeSql(String dbId, String strSql) throws DataException {
        DbLinkEntity link = dblinkService.getInfo(dbId);
        int result = 0;
        try {
            @Cleanup Connection conn=null;
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    result = JdbcUtil.custom(conn, strSql);
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
        return result;
    }

    @Override
    public void delete(String dbId, String table) throws DataException {
        String tenSource = "";
        if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
            tenSource = userProvider.get().getTenantDbConnectionString();
        }
        try {
            @Cleanup Connection conn=null;
            DbLinkEntity link = dblinkService.getInfo(dbId);
            if (link != null) {
               conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    StringBuilder sql = new StringBuilder();
                    sql.append("drop table " + table);
                    JdbcUtil.custom(conn, sql.toString());
                }
            } else {
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , tenSource);
                if (StringUtil.isEmpty(tenSource)) {
                    String dbName = dataSourceUtils.getDbName();
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                if (conn != null) {
                    StringBuilder sql = new StringBuilder();
                    sql.append("drop table " + table);
                    JdbcUtil.custom(conn, sql.toString());
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public ActionResult create(String dbId, DbTableModel dbTableModel, List<DbTableFieldModel> tableFieldList) throws DataException {
        DbLinkEntity link = dblinkService.getInfo(dbId);
        String tenSource = "";
        if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
            tenSource = userProvider.get().getTenantDbConnectionString();
        }
        try {
            @Cleanup Connection conn = null;
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        DbUtil.createMySqlTable(conn, dbTableModel, tableFieldList);
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        DbUtil.createSqlserverTable(conn, dbTableModel, tableFieldList);
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        DbUtil.createOrcalTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    } else if (link.getDbType().toLowerCase().equals(DbType.DM.getDb())) {
                        DbUtil.createDmTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }

                } else {
                    return ActionResult.fail("连接失败");
                }
            } else {
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , tenSource);
                if (StringUtil.isEmpty(tenSource)) {
                    String dbName = dataSourceUtils.getDbName();
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                if (conn != null) {
                    if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.MYSQL.getDb())) {
                        DbUtil.createMySqlTable(conn, dbTableModel, tableFieldList);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.SQL_SERVER.getDb())) {
                        DbUtil.createSqlserverTable(conn, dbTableModel, tableFieldList);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.ORACLE.getDb())) {
                        DbUtil.createOrcalTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.DM.getDb())) {
                        DbUtil.createDmTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }
                } else {
                    return ActionResult.fail("连接失败");
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
        return ActionResult.success("新建成功");
    }

    @Override
    public void update(String dbId, DbTableModel dbTableModel, List<DbTableFieldModel> tableFieldList) throws DataException {
        String tenSource = "";
        if (Boolean.parseBoolean(configValueUtil.getMultiTenancy())) {
            tenSource = userProvider.get().getTenantDbConnectionString();
        }
        try {
            @Cleanup Connection conn = null;
            DbLinkEntity link = dblinkService.getInfo(dbId);
            if (link != null) {
                conn = JdbcUtil.getConn(link.getDbType().toLowerCase(), link.getUserName(), link.getPassword(), link.getHost(), link.getPort(), link.getServiceName());
                if (conn != null) {
                    if (link.getDbType().toLowerCase().equals(DbType.MYSQL.getDb())) {
                        DbUtil.updateMySqlTable(conn, dbTableModel, tableFieldList);
                    } else if (link.getDbType().toLowerCase().equals(DbType.SQL_SERVER.getDb())) {
                        DbUtil.updateSqlserverTable(conn, dbTableModel, tableFieldList);
                    } else if (link.getDbType().toLowerCase().equals(DbType.ORACLE.getDb())) {
                        DbUtil.updateOrcalTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    } else if (link.getDbType().toLowerCase().equals(DbType.DM.getDb())) {
                        DbUtil.updateDmTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }
                }
            } else {
                String urll = dataSourceUtils.getUrl().replace("{dbName}" , tenSource);
                if (StringUtil.isEmpty(tenSource)) {
                    String dbName = dataSourceUtils.getDbName();
                    urll = dataSourceUtils.getUrl().replace("{dbName}" , dbName);
                }
                conn = JdbcUtil.getConn(dataSourceUtils.getUserName(), dataSourceUtils.getPassword(), urll);
                if (conn != null) {
                    if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.MYSQL.getDb())) {
                        DbUtil.updateMySqlTable(conn, dbTableModel, tableFieldList);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.SQL_SERVER.getDb())) {
                        DbUtil.updateSqlserverTable(conn, dbTableModel, tableFieldList);
                    } else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.ORACLE.getDb())) {
                        DbUtil.updateOrcalTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }  else if (dataSourceUtils.getUrl().toLowerCase().contains(DbType.DM.getDb())) {
                        DbUtil.updateDmTable(conn, dbTableModel, tableFieldList, dataSourceUtil.getTableSpace());
                    }
                }
            }
        } catch (Exception e) {
            throw new DataException(e.getMessage());
        }
    }

}
